package Controller;

/**
*
* @author Nhóm java lớp PNV2T
* 				- Nguyễn Thị Lệ Đài
* 				- Nguyễn Chí Hướng
* 
* Connect to database and some function help us easier do with exercise
*/
import java.io.FileInputStream;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.CallableStatement;
import java.sql.ResultSetMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ConnectDB {

	public static final int MODE_PREPARED = 0;
	public static final int MODE_CALLABLE = 1;
	public static final int MODE_NORMAL = 2;

	private PreparedStatement pre = null;
	private Connection con = null;
	private Statement stat = null;
	private ResultSet rs = null;

	private int mode = MODE_NORMAL;

	public ConnectDB() throws Exception {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException ex) {
			Logger.getLogger(ConnectDB.class.getName()).log(Level.SEVERE, null,
					ex);
			ex.printStackTrace();
		}
	}

	/**
	 * 
	 * DBFacade Constructor, used to create a driver-customized DBFacade object.
	 * 
	 * @param driver
	 *            - the specific driver
	 * 
	 */
	public ConnectDB(String Driver) throws Exception {
		Class.forName(Driver);
	}

	/**
	 * open(String url, String username, String pass): Open connection
	 * 
	 * @return a Connection
	 * @param url
	 *            - URL to the database
	 * @param username
	 *            - user name for authentication
	 * @param pass
	 *            - password
	 */
	public boolean open() throws Exception {
		boolean temp = false;
		Properties p = new Properties();
		try {
			FileInputStream fin = new FileInputStream("connection.cfg");
			p.load(fin);
		} catch (Exception e) {

		}

		String server = p.getProperty("ServerID");
		String port = p.getProperty("Port");
		String databaseName = p.getProperty("Database");
		String username = p.getProperty("Username");
		String password = p.getProperty("Password");

		String sql = "jdbc:sqlserver://" + server + ":" + port
				+ ";databaseName=" + databaseName + ";User=" + username
				+ ";Password=" + password;

		try {
			con = DriverManager.getConnection(sql);
			temp = true;
		} catch (SQLException ex) {
			System.err.println("Connect Database Fail !");
			System.err.println(sql);
			temp = false;
		}
		return temp;
	}

	/**
	 * Close connection
	 * 
	 * @throws Exception
	 */
	public void close() throws Exception {
		if (rs != null) {
			rs.close();
		}
		if (pre != null) {
			pre.close();
		}
		if (con != null) {
			con = null;
		}
	}

	/**
	 * Execute the sql query
	 * 
	 * @param sql
	 *            - String of SQL.
	 */
	public ResultSet doQuery(String sql) throws SQLException {
		rs = null;
		if (mode == MODE_NORMAL) {
			stat = con.createStatement();
			rs = stat.executeQuery(sql);
		}
		return rs;
	}
	

	/**
	 * Execute preStatement and return data
	 * @return
	 * @throws java.sql.SQLException
	 */
	public ResultSet doQuery() throws SQLException {
		rs = null;
		if (pre != null) {
			rs = pre.executeQuery();
			pre = null;
		}
		return rs;
	}

	/**
	 * Update query for normal sql statement
	 * 
	 * @param sql
	 *            - String
	 * @return
	 * @throws java.sql.SQLException
	 */
	public int doUpdate(String sql) throws SQLException {
		if (mode == MODE_NORMAL) {
			stat = con.createStatement();
			return stat.executeUpdate(sql);
		} else {
			return 0;
		}
	}

	/**
	 * Update query for preStatement sql
	 * 
	 * @return
	 * @throws SQLException
	 */
	public int doUpdate() throws SQLException {
		int rows = 0;
		if (pre != null) {
			rows = pre.executeUpdate();
		}
		return rows;
	}

	/*
	 * Set and get some primary data type - Set with mapping of value and key -
	 * Help to host all data be logic and easy set data into preStatement
	 */
	public void setDouble(int idx, double value) throws SQLException {
		pre.setDouble(idx, value);
	}

	public void setLong(int idx, long value) throws SQLException {
		pre.setLong(idx, value);
	}

	public void setInt(int idx, int value) throws SQLException {
		pre.setInt(idx, value);
	}

	public int getInt(int idx) throws SQLException {
		return ((CallableStatement) pre).getInt(idx);
	}

	public void setString(int idx, String value) throws SQLException {
		pre.setString(idx, value);
	}

	public String getString(int idx) throws SQLException {
		return ((CallableStatement) pre).getString(idx);
	}

	public void setBoolean(int idx, boolean value) throws SQLException {
		pre.setBoolean(idx, value);
	}

	public boolean getBoolean(int idx) throws SQLException {
		return ((CallableStatement) pre).getBoolean(idx);
	}

	/**
	 * Set sql statement for preStatement
	 * @param sql
	 * @throws SQLException
	 */
	public void setStatement(String sql) throws SQLException {
		if (con != null) {
			if (mode == MODE_PREPARED) {
				pre = con.prepareStatement(sql);
			}
			if (mode == MODE_CALLABLE) {
				pre = con.prepareCall(sql);
			}
		}
	}

	/**
	 * @return status of sql statement execution 
	 * @throws SQLException
	 */
	public boolean doExecute() throws SQLException {
		boolean bool = false;
		if (pre != null) {
			bool = pre.execute();
		}
		return bool;
	}

	/**
	 * Set type of sql statement
	 * 	- normal
	 * 	- preStatement
	 *  - callable to database stored procedures
	 * @param mod
	 */
	public void setMode(int mod) {
		this.mode = mod;
	}

}
